﻿using System.Data;
using System.Data.SqlClient;
using System.Globalization;
using ContractManagementSystem.Entity;

namespace ContractManagementSystem.DataAccess
{
    public class MessageDataAccess : LogDataAccess
    {
        private const string UserName = "Admin";

        public bool Insert(MessageEntity messageEntity)
        {
            if (!string.IsNullOrEmpty(ErrorMessage))
            {
                //log error
                WriteError(ErrorMessage, UserName);
                return false;
            }

            try
            {
                //log event          
                WriteEvent("Inserting...", UserName);

                var sqlCommand = new SqlCommand("sp_Message_Insert", CreateConnection())
                                     {CommandType = CommandType.StoredProcedure};

                sqlCommand.Parameters.AddWithValue("@fromUserId", messageEntity.FromUserId);
                sqlCommand.Parameters.AddWithValue("@toUserId", messageEntity.ToUserId);
                sqlCommand.Parameters.AddWithValue("@content", messageEntity.Content);
                
                sqlCommand.ExecuteNonQuery();
            }
            catch(SqlException e)
            {
                ErrorMessage = e.ToString();

                //log event
                WriteEvent("Insert fail!", UserName);

                //log error
                WriteError(ErrorMessage, UserName);

                return false;
            }
            finally
            {
                CloseConnection();
            }

            //log event
            WriteEvent("Insert success!", UserName);

            return true;
        }

        public bool Update(MessageEntity messageEntity)
        {
            if (!string.IsNullOrEmpty(ErrorMessage))
            {
                //log error
                WriteError(ErrorMessage, UserName);
                return false;
            }

            try
            {
                //log event                
                WriteEvent("Updating...", UserName);

                var sqlCommand = new SqlCommand("sp_Message_Update", CreateConnection())
                                     {CommandType = CommandType.StoredProcedure};

                sqlCommand.Parameters.AddWithValue("@fromUserId", messageEntity.FromUserId);
                sqlCommand.Parameters.AddWithValue("@toUserId", messageEntity.ToUserId);
                sqlCommand.Parameters.AddWithValue("@content", messageEntity.Content);
                sqlCommand.ExecuteNonQuery();
            }
            catch(SqlException e)
            {
                ErrorMessage = e.ToString();

                //log event
                WriteEvent("Update fail!", UserName);

                //log error
                WriteError(ErrorMessage, UserName);

                return false;
            }
            finally
            {
                CloseConnection();
            }

            //log event
            WriteEvent("Update success!", UserName);

            return true;
        }

        public bool Delete(DataTable source)
        {
            if (!string.IsNullOrEmpty(ErrorMessage))
            {
                //log error
                WriteError(ErrorMessage, UserName);
                return false;
            }

            try
            {
                //log event                
                WriteEvent("Deleting...", UserName);
                using (var sqlCommand = new SqlCommand())
                {
                    sqlCommand.CommandType = CommandType.StoredProcedure;
                    sqlCommand.UpdatedRowSource = UpdateRowSource.None;
                    sqlCommand.CommandText = "sp_Message_Delete";
                    sqlCommand.Parameters.Add(new SqlParameter("@id", 
                        SqlDbType.UniqueIdentifier, 16, source.Columns["MessageId"].ColumnName));
                    sqlCommand.Connection = CreateConnection();
                    var sqlDataAdapter = new SqlDataAdapter { InsertCommand = sqlCommand, UpdateBatchSize = source.Rows.Count };
                    sqlDataAdapter.Update(source);
                }
            }
            catch(SqlException e)
            {
                ErrorMessage = e.ToString();

                //log event
                WriteEvent("Delete fail!", UserName);

                //log error
                WriteError(ErrorMessage, UserName);

                return false;

            }
            finally
            {
                CloseConnection();
            }

            //log event
            WriteEvent("Delete success!", UserName);

            return true;
        }

        public DataTable FetchAll(string id,int type)
        {
            if (!string.IsNullOrEmpty(ErrorMessage))
            {
                //log error
                WriteError(ErrorMessage, UserName);
                return null;
            }

            DataTable result = null;
            try
            {
                //log event                
                WriteEvent("Selecting...", UserName);

                using (var sqlCommand = new SqlCommand("sp_Message_Select", CreateConnection()))
                {
                    sqlCommand.CommandType = CommandType.StoredProcedure;
                    sqlCommand.Parameters.AddWithValue("@id", id);
                    sqlCommand.Parameters.AddWithValue("@type", type);

                    var adapter = new SqlDataAdapter(sqlCommand);
                    result = new DataTable { Locale = CultureInfo.CurrentCulture };
                    adapter.Fill(result);
                    ErrorMessage = null;
                }
            }
            catch (SqlException sqlException)
            {
                ErrorMessage = sqlException.Message;

                //log event
                WriteEvent("Select fail!", UserName);

                //log error
                WriteError(ErrorMessage, UserName);

                return result;
            }
            finally
            {
                CloseConnection();
            }

            //log event
            WriteEvent("Select success!", UserName);

            return result;
        }

        public int FetchNumber(string id)
        {
            if (!string.IsNullOrEmpty(ErrorMessage))
            {
                //log error
                WriteError(ErrorMessage, UserName);
                return 0;
            }

            int result;
            try
            {
                //log event                
                WriteEvent("Selecting...", UserName);

                using (var sqlCommand = new SqlCommand("sp_Message_SelectNewNumber", CreateConnection()))
                {
                    sqlCommand.CommandType = CommandType.StoredProcedure;
                    sqlCommand.Parameters.AddWithValue("@userName", id);

                    result = int.Parse(sqlCommand.ExecuteScalar().ToString(),CultureInfo.CurrentCulture);
                    ErrorMessage = null;
                }
            }
            catch (SqlException sqlException)
            {
                ErrorMessage = sqlException.Message;

                //log event
                WriteEvent("Select fail!", UserName);

                //log error
                WriteError(ErrorMessage, UserName);

                return 0;
            }
            finally
            {
                CloseConnection();
            }

            //log event
            WriteEvent("Select success!", UserName);

            return result;
        }
    }
}
